import pymysql.cursors
import json
class log:
    id = 0
    application = ''
    logged = ''
    level = ''
    message = ''
    logger = ''
    callsite = ''
    exception = ''

    def __init__(self,id = 0,application = '',logged = '',level = '',message = '',logger = '',callsite = '',exception = ''):
        self.id=id,
        self.application = application
        self.logged = logged
        self.level = level
        self.message = message
        self.logger = logger
        self.callsite = callsite
        self.exception = exception

class dblog_brokerinfo:
    __conn=False
    def __init__(self):
        self.__conn = pymysql.connect(host='localhost',
                             user='root',
                             password='123456',
                             db='jizhi_apigateway',
                             charset='utf8',
                             cursorclass=pymysql.cursors.DictCursor)
    def __del__(self):
        self.__conn.close()

    def Insert(self,model):
        print(model)
        try:
            sql = "insert into log_brokerinfo(AvailMoney,MarketCap,TotalAssets,BrokerGuid,BrokerUser,UserGuid) values('{}','{}','{}','{}','{}','{}');".format(model['AvailMoney'],model['MarketCap'],model['TotalAssets'],model['BrokerGuid'],model['BrokerUser'],model['UserGuid'])
            cursor = self.__conn.cursor()
            cursor.execute(sql)
            self.__conn.commit()
            return  cursor.rowcount
        except Exception as err:
            print(err)
            self.__conn.rollback()
            return 0


class dbLog:
    __conn=False
    def __init__(self):
        self.__conn = pymysql.connect(host='47.106.112.202',
                             user='apigateway',
                             password='apigateway',
                             db='jizhi_apigateway',
                             charset='utf8',
                             cursorclass=pymysql.cursors.DictCursor)
    def __del__(self):
        self.__conn.close()
        

    def Query(self):
        sql = "select * from log where locate('broInfo.AMessage',Message) and logged>CURRENT_DATE;"
        
        cursor = self.__conn.cursor()
        dataList = []
        try:
            cursor.execute(sql)
            dataList = cursor.fetchall()
        except Exception as err:
            print("查询信息出错%s"%err)
        cursor.close()
        return dataList


def sql_fetch_json(cursor: pymysql.cursors.Cursor):
    keys = []
    for column in cursor.description:
        keys.append(column[0])
    key_number = len(keys)

    json_data = [] 
    for row in cursor.fetchall():
        item = dict()
        for q in range(key_number):
            item[keys[q]] = row[q]
        json_data.append(item)

    return json_data

def main():
    db = dbLog()
    list = db.Query()
    jsonList = []
    for row in list:
        str = row['Message'].replace('broInfo.AMessage:','')
        jsonList.append(json.loads(str))
    for item in jsonList:
        Insert(item)

def Insert(json):
    db = dblog_brokerinfo()
    result = db.Insert(json)
    print("成功插入{}条数据".format(result))
main()
